const express = require('express');
const router = express.Router();
const pool = require('../config/database');
const multer = require('multer');
const path = require('path');

// 配置 multer 进行文件存储
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, 'uploads/'); // 图片将存储在 backend/uploads 目录下
  },
  filename: (req, file, cb) => {
    cb(null, Date.now() + path.extname(file.originalname)); // 使用时间戳作为文件名以避免重复
  },
});

const upload = multer({ storage: storage });

// 获取数据概览
router.get('/dashboard', async (req, res) => {
  try {
    // 获取总销售额（只统计已完成的订单）
    const [salesResult] = await pool.execute(
      'SELECT COALESCE(SUM(total_price), 0) as total FROM orders WHERE status = "completed"'
    );
    const totalSales = Number(salesResult[0].total);

    // 获取总订单数
    const [ordersResult] = await pool.execute('SELECT COUNT(*) as count FROM orders');
    const totalOrders = Number(ordersResult[0].count);

    // 获取用户总数
    const [usersResult] = await pool.execute('SELECT COUNT(*) as count FROM users');
    const totalUsers = Number(usersResult[0].count);

    // 获取商品总数
    const [productsResult] = await pool.execute('SELECT COUNT(*) as count FROM drugs');
    const totalProducts = Number(productsResult[0].count);

    // 获取最近订单（最近10条）
    const [recentOrders] = await pool.execute(`
      SELECT o.id, o.total_price, o.status, o.created_at,
             u.username
      FROM orders o
      JOIN users u ON o.user_id = u.id
      ORDER BY o.created_at DESC
      LIMIT 10
    `);

    // 获取库存预警（库存少于10的药品）
    const [lowStockDrugs] = await pool.execute(`
      SELECT id, name, stock, price, category,
             CASE 
               WHEN stock = 0 THEN '无库存'
               WHEN stock < 5 THEN '严重不足'
               ELSE '库存不足'
             END as stock_status
      FROM drugs
      WHERE stock < 10
      ORDER BY stock ASC
    `);

    // 获取近7天销售额趋势
    const [salesTrend] = await pool.execute(`
      SELECT DATE(created_at) as date, COALESCE(SUM(total_price), 0) as total
      FROM orders
      WHERE status = 'completed'
      AND created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
      GROUP BY DATE(created_at)
      ORDER BY date ASC
    `);

    // 获取商品分类统计
    const [categoryStats] = await pool.execute(`
      SELECT category, COUNT(*) as count
      FROM drugs
      GROUP BY category
    `);

    // 格式化最近订单数据
    const formattedRecentOrders = recentOrders.map(order => ({
      orderNumber: order.id,
      username: order.username,
      totalAmount: Number(order.total_price).toFixed(2),
      status: order.status,
      statusText: {
        'pending': '待处理',
        'processing': '处理中',
        'completed': '已完成',
        'cancelled': '已取消'
      }[order.status] || order.status,
      createTime: new Date(order.created_at).toLocaleString('zh-CN', {
        year: 'numeric',
        month: '2-digit',
        day: '2-digit',
        hour: '2-digit',
        minute: '2-digit'
      })
    }));

    // 格式化库存预警数据
    const formattedLowStockDrugs = lowStockDrugs.map(drug => ({
      id: drug.id,
      name: drug.name,
      stock: drug.stock,
      price: Number(drug.price).toFixed(2),
      category: drug.category,
      stockStatus: drug.stock_status
    }));

    res.json({
      totalSales,
      totalOrders,
      totalUsers,
      totalProducts,
      recentOrders: formattedRecentOrders,
      lowStockDrugs: formattedLowStockDrugs,
      salesTrend,
      categoryStats
    });
  } catch (error) {
    console.error('获取数据概览失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取所有药品 (管理员)
router.get('/drugs', async (req, res) => {
  try {
    const { name, category } = req.query;
    let query = 'SELECT * FROM drugs';
    const params = [];

    const conditions = [];

    if (name) {
      conditions.push('name LIKE ?');
      params.push(`%${name}%`);
    }
    if (category) {
      conditions.push('category = ?');
      params.push(category);
    }

    if (conditions.length > 0) {
      query += ' WHERE ' + conditions.join(' AND ');
    }

    const [rows] = await pool.execute(query, params);
    res.json(rows);
  } catch (error) {
    console.error('获取药品列表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 添加药品 (管理员) - 支持文件上传
router.post('/drugs', upload.single('image'), async (req, res) => {
  const { name, description, price, stock, category } = req.body;
  const image = req.file ? `/uploads/${req.file.filename}` : null; // 保存相对路径
  try {
    const [result] = await pool.execute(
      'INSERT INTO drugs (name, description, price, stock, image, category) VALUES (?, ?, ?, ?, ?, ?)',
      [name, description, price, stock, image, category]
    );
    res.status(201).json({ id: result.insertId, message: '药品添加成功' });
  } catch (error) {
    console.error('添加药品失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 更新药品 (管理员) - 支持文件上传
router.put('/drugs/:id', upload.single('image'), async (req, res) => {
  const { id } = req.params;
  const { name, description, price, stock, category } = req.body;
  const image = req.file ? `/uploads/${req.file.filename}` : req.body.image; // 如果有新文件上传则使用新文件，否则使用原有图片路径
  try {
    const [result] = await pool.execute(
      'UPDATE drugs SET name = ?, description = ?, price = ?, stock = ?, image = ?, category = ? WHERE id = ?',
      [name, description, price, stock, image, category, id]
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '药品未找到' });
    }
    res.json({ message: '药品更新成功' });
  } catch (error) {
    console.error('更新药品失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 删除药品 (管理员)
router.delete('/drugs/:id', async (req, res) => {
  const { id } = req.params;
  try {
    const [result] = await pool.execute('DELETE FROM drugs WHERE id = ?', [id]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '药品未找到' });
    }
    res.json({ message: '药品删除成功' });
  } catch (error) {
    console.error('删除药品失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取所有订单 (管理员)
router.get('/orders', async (req, res) => {
  try {
    const [orders] = await pool.execute(`
      SELECT o.*, u.username, u.email
      FROM orders o
      LEFT JOIN users u ON o.user_id = u.id
      ORDER BY o.created_at DESC
    `);
    res.json(orders);
  } catch (error) {
    console.error('获取订单列表失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 更新订单状态 (管理员)
router.put('/orders/:id/status', async (req, res) => {
  const { id } = req.params;
  const { status } = req.body;
  
  try {
    // 获取当前订单状态
    const [orderRows] = await pool.execute(
      'SELECT status FROM orders WHERE id = ?',
      [id]
    );

    if (orderRows.length === 0) {
      return res.status(404).json({ message: '订单未找到' });
    }

    const currentStatus = orderRows[0].status;

    // 验证状态转换是否合法
    const validTransitions = {
      'pending': ['processing', 'cancelled'],
      'processing': ['completed', 'cancelled'],
      'completed': [],
      'cancelled': []
    };

    if (!validTransitions[currentStatus]?.includes(status)) {
      return res.status(400).json({ 
        message: `不允许从 ${currentStatus} 状态转换到 ${status} 状态` 
      });
    }

    // 更新订单状态
    const [result] = await pool.execute(
      'UPDATE orders SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
      [status, id]
    );

    res.json({ message: '订单状态更新成功' });
  } catch (error) {
    console.error('更新订单状态失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取订单详情 (管理员)
router.get('/orders/:id', async (req, res) => {
  const { id } = req.params;
  try {
    const [orders] = await pool.execute(`
      SELECT o.*, u.username, u.email
      FROM orders o
      LEFT JOIN users u ON o.user_id = u.id
      WHERE o.id = ?
    `, [id]);
    
    if (orders.length === 0) {
      return res.status(404).json({ message: '订单未找到' });
    }

    const [orderItems] = await pool.execute(`
      SELECT oi.*, d.name as drug_name, d.image as drug_image
      FROM order_items oi
      LEFT JOIN drugs d ON oi.drug_id = d.id
      WHERE oi.order_id = ?
    `, [id]);

    const order = orders[0];
    order.items = orderItems;
    
    res.json(order);
  } catch (error) {
    console.error('获取订单详情失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

module.exports = router; 